'\" t
.\"     Title: \fBmysqlreplicate\fR
.\"    Author: [FIXME: author] [see http://docbook.sf.net/el/author]
.\" Generator: DocBook XSL Stylesheets v1.79.1 <http://docbook.sf.net/>
.\"      Date: 01/14/2017
.\"    Manual: MySQL Utilities
.\"    Source: MySQL 1.6.4
.\"  Language: English
.\"
.TH "\FBMYSQLREPLICATE\FR" "1" "01/14/2017" "MySQL 1\&.6\&.4" "MySQL Utilities"
.\" -----------------------------------------------------------------
.\" * Define some portability stuff
.\" -----------------------------------------------------------------
.\" ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
.\" http://bugs.debian.org/507673
.\" http://lists.gnu.org/archive/html/groff/2009-02/msg00013.html
.\" ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
.ie \n(.g .ds Aq \(aq
.el       .ds Aq '
.\" -----------------------------------------------------------------
.\" * set default formatting
.\" -----------------------------------------------------------------
.\" disable hyphenation
.nh
.\" disable justification (adjust text to left margin only)
.ad l
.\" -----------------------------------------------------------------
.\" * MAIN CONTENT STARTS HERE *
.\" -----------------------------------------------------------------
.SH "NAME"
mysqlreplicate \- Set Up and Start Replication Between Two Servers
.SH "SYNOPSIS"
.HP \w'\fBmysqlreplicate\ [\fR\fB\fIoptions\fR\fR\fB]\fR\ 'u
\fBmysqlreplicate [\fR\fB\fIoptions\fR\fR\fB]\fR
.SH "DESCRIPTION"
.PP
This utility permits an administrator to setup and start replication from one server (the master) to another (the slave)\&. The user provides login information for the slave and connection information for connecting to the master\&. It is also possible to specify a database to be used to test replication\&.
.PP
The utility reports conditions where the storage engines on the master and the slave differ for older versions of the server\&. It also reports a warning if the InnoDB storage engine type (plugin verus built\-in) differs on the master and slave\&. For InnoDB to be the same, both servers must be running the same "type" of InnoDB (built\-in or the InnoDB Plugin), and InnoDB on both servers must have the same major and minor version numbers and enabled state\&.
.PP
By default, the utility issues warnings for mismatches between the sets of storage engines, the default storage engine, and the InnoDB storage engine\&. To produce errors instead, use the
\fB\-\-pedantic\fR
option, which requires storage engines to be the same on the master and slave\&.
.PP
The
\fB\-vv\fR
option displays any discrepancies between the storage engines and InnoDB values, with or without the
\fB\-\-pedantic\fR
option\&.
.PP
Replication can be started using one of the following strategies\&.
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
Start from the current position (default)
.sp
Start replication from the current master binary log file and position\&. The utility uses the
SHOW MASTER STATUS
statement to retrieve this information\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
Start from the beginning
.sp
Start replication from the first event recorded in the master binary log\&. To do this, use the
\fB\-\-start\-from\-beginning\fR
option\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
Start from a binary log file
.sp
Start replication from the first event in a specific master binary log file\&. To do this, use the
\fB\-\-master\-log\-file\fR
option\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
Start from a specific event
.sp
Start replication from specific event coordinates (specific binary log file and position)\&. To do this, use the
\fB\-\-master\-log\-file\fR
and
\fB\-\-master\-log\-pos\fR
options\&.
.RE
OPTIONS.PP
\fBmysqlreplicate\fR
accepts the following command\-line options:
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-help
.sp
Display a help message and exit\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-license
.sp
Display license information and exit\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-master=\fImaster\fR
.sp
Connection information for the master server\&.
.sp
To connect to a server, it is necessary to specify connection parameters such as the user name, host name, password, and either a port or socket\&. MySQL Utilities provides a number of ways to supply this information\&. All of the methods require specifying your choice via a command\-line option such as \-\-server, \-\-master, \-\-slave, etc\&. The methods include the following in order of most secure to least secure\&.
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
Use login\-paths from your
\&.mylogin\&.cnf
file (encrypted, not visible)\&. Example :
\fIlogin\-path\fR[:\fIport\fR][:\fIsocket\fR]
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
Use a configuration file (unencrypted, not visible) Note: available in release\-1\&.5\&.0\&. Example :
\fIconfiguration\-file\-path\fR[:\fIsection\fR]
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
Specify the data on the command\-line (unencrypted, visible)\&. Example :
\fIuser\fR[:\fIpasswd\fR]@\fIhost\fR[:\fIport\fR][:\fIsocket\fR]
.RE
.sp
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
login\-path (\&.mylogin\&.cnf) :
\fIlogin\-path\fR[:\fIport\fR][:\fIsocket\fR]
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
Configuration file :
\fIconfiguration\-file\-path\fR[:\fIsection\fR]
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
Command\-line :
\fIuser\fR[:\fIpasswd\fR]@\fIhost\fR[:\fIport\fR][:\fIsocket\fR]
.RE
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-master\-log\-file=\fImaster_log_file\fR
.sp
Begin replication from the beginning of this master log file\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-master\-log\-pos=\fImaster_log_pos\fR
.sp
Begin replication from this position in the master log file\&. This option is not valid unless
\fB\-\-master\-log\-file\fR
is given\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-pedantic, \-p
.sp
Fail if both servers do not have the same set of storage engines, the same default storage engine, and the same InnoDB storage engine\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-rpl\-user=\fIreplication_user\fR
.sp
The user and password for the replication user, in the format:
\fIuser\fR[:\fIpassword\fR] or
\fIlogin\-path\fR\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-slave=\fIslave\fR
.sp
Connection information for the slave server\&. You may specify only one slave for this option\&.
.sp
To connect to a server, it is necessary to specify connection parameters such as the user name, host name, password, and either a port or socket\&. MySQL Utilities provides a number of ways to supply this information\&. All of the methods require specifying your choice via a command\-line option such as \-\-server, \-\-master, \-\-slave, etc\&. The methods include the following in order of most secure to least secure\&.
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
Use login\-paths from your
\&.mylogin\&.cnf
file (encrypted, not visible)\&. Example :
\fIlogin\-path\fR[:\fIport\fR][:\fIsocket\fR]
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
Use a configuration file (unencrypted, not visible) Note: available in release\-1\&.5\&.0\&. Example :
\fIconfiguration\-file\-path\fR[:\fIsection\fR]
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
Specify the data on the command\-line (unencrypted, visible)\&. Example :
\fIuser\fR[:\fIpasswd\fR]@\fIhost\fR[:\fIport\fR][:\fIsocket\fR]
.RE
.sp
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-start\-from\-beginning, \-b
.sp
Start replication at the beginning of events logged in the master binary log\&. This option is not valid unless both
\fB\-\-master\-log\-file\fR
and
\fB\-\-master\-log\-pos\fR
are given\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-ssl\-ca
.sp
The path to a file that contains a list of trusted SSL CAs\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-ssl\-cert
.sp
The name of the SSL certificate file to use for establishing a secure connection\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-ssl\-key
.sp
The name of the SSL key file to use for establishing a secure connection\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-ssl
.sp
Specifies if the server connection requires use of SSL\&. If an encrypted connection cannot be established, the connection attempt fails\&. Default setting is 0 (SSL not required)\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-test\-db=\fItest_database\fR
.sp
The database name to use for testing the replication setup\&. If this option is not given, no testing is done, only error checking\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-verbose, \-v
.sp
Specify how much information to display\&. Use this option multiple times to increase the amount of information\&. For example,
\fB\-v\fR
= verbose,
\fB\-vv\fR
= more verbose,
\fB\-vvv\fR
= debug\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-version
.sp
Display version information and exit\&.
.RE
NOTES.PP
The login user for the master server must have the appropriate permissions to grant access to all databases, and have the ability to create user accounts\&. For example, the user account used to connect to the master must have the
\fBWITH GRANT OPTION\fR
privilege\&.
.PP
The server IDs on the master and slave must be nonzero and unique\&. The utility reports an error if the server ID is 0 on either server or the same on the master and slave\&. Set these values before starting this utility\&.
.PP
Mixing IP and hostnames is not recommended\&. The replication\-specific utilities attempt to compare hostnames and IP addresses as aliases for checking slave connectivity to the master\&. However, if your installation does not support reverse name lookup, the comparison could fail\&. Without the ability to do a reverse name lookup, the replication utilities could report a false negative that the slave is (not) connected to the master\&.
.PP
For example, if you setup replication using "MASTER_HOST=ubuntu\&.net" on the slave and later connect to the slave with
\fBmysqlrplcheck\fR
and have the master specified as "\-\-master=192\&.168\&.0\&.6" using the valid IP address for "ubuntu\&.net", you must have the ability to do a reverse name lookup to compare the IP (192\&.168\&.0\&.6) and the hostname (ubuntu\&.net) to determine if they are the same machine\&.
.PP
The path to the MySQL client tools should be included in the
PATH
environment variable in order to use the authentication mechanism with login\-paths\&. This permits the utility to use the
\fBmy_print_defaults\fR
tools which is required to read the login\-path values from the login configuration file (\&.mylogin\&.cnf)\&.
EXAMPLES.PP
To set up replication between two MySQL instances running on different ports of the same host using the default settings, use this command:
.sp
.if n \{\
.RS 4
.\}
.nf
shell> \fBmysqlreplicate \-\-master=root@localhost:3306 \e\fR
          \fB\-\-slave=root@localhost:3307 \-\-rpl\-user=rpl:rpl\fR
# master on localhost: \&.\&.\&. connected\&.
# slave on localhost: \&.\&.\&. connected\&.
# Checking for binary logging on master\&.\&.\&.
# Setting up replication\&.\&.\&.
# \&.\&.\&.done\&.
.fi
.if n \{\
.RE
.\}
.PP
The following command uses
\fB\-\-pedantic\fR
to ensure that replication between the master and slave is successful if and only if both servers have the same storage engines available, the same default storage engine, and the same InnoDB storage engine:
.sp
.if n \{\
.RS 4
.\}
.nf
shell> \fBmysqlreplicate \-\-master=root@localhost:3306 \e\fR
          \fB\-\-slave=root@localhost:3307 \-\-rpl\-user=rpl:rpl \-vv \-\-pedantic\fR
# master on localhost: \&.\&.\&. connected\&.
# slave on localhost: \&.\&.\&. connected\&.
# master id = 2
#  slave id = 99
# Checking InnoDB statistics for type and version conflicts\&.
# Checking storage engines\&.\&.\&.
# Checking for binary logging on master\&.\&.\&.
# Setting up replication\&.\&.\&.
# Flushing tables on master with read lock\&.\&.\&.
# Connecting slave to master\&.\&.\&.
# CHANGE MASTER TO MASTER_HOST = [\&.\&.\&.omitted\&.\&.\&.]
# Starting slave\&.\&.\&.
# status: Waiting for master to send event
# error: 0:
# Unlocking tables on master\&.\&.\&.
# \&.\&.\&.done\&.
.fi
.if n \{\
.RE
.\}
.PP
The following command starts replication from the current position of the master (which is the default):
.sp
.if n \{\
.RS 4
.\}
.nf
shell> \fBmysqlreplicate \-\-master=root@localhost:3306 \e\fR
          \fB\-\-slave=root@localhost:3307 \-\-rpl\-user=rpl:rpl\fR
 # master on localhost: \&.\&.\&. connected\&.
 # slave on localhost: \&.\&.\&. connected\&.
 # Checking for binary logging on master\&.\&.\&.
 # Setting up replication\&.\&.\&.
 # \&.\&.\&.done\&.
.fi
.if n \{\
.RE
.\}
.PP
The following command starts replication from the beginning of recorded events on the master:
.sp
.if n \{\
.RS 4
.\}
.nf
shell> \fBmysqlreplicate \-\-master=root@localhost:3306 \e\fR
     \fB\-\-slave=root@localhost:3307 \-\-rpl\-user=rpl:rpl \e\fR
     \fB\-\-start\-from\-beginning\fR
 # master on localhost: \&.\&.\&. connected\&.
 # slave on localhost: \&.\&.\&. connected\&.
 # Checking for binary logging on master\&.\&.\&.
 # Setting up replication\&.\&.\&.
 # \&.\&.\&.done\&.
.fi
.if n \{\
.RE
.\}
.PP
The following command starts replication from the beginning of a specific master binary log file:
.sp
.if n \{\
.RS 4
.\}
.nf
shell> \fBmysqlreplicate \-\-master=root@localhost:3306 \e\fR
          \fB\-\-slave=root@localhost:3307 \-\-rpl\-user=rpl:rpl \e\fR
          \fB\-\-master\-log\-file=my_log\&.000003\fR
 # master on localhost: \&.\&.\&. connected\&.
 # slave on localhost: \&.\&.\&. connected\&.
 # Checking for binary logging on master\&.\&.\&.
 # Setting up replication\&.\&.\&.
 # \&.\&.\&.done\&.
.fi
.if n \{\
.RE
.\}
.PP
The following command starts replication from specific master binary log coordinates (specific log file and position):
.sp
.if n \{\
.RS 4
.\}
.nf
shell> \fBmysqlreplicate \-\-master=root@localhost:3306 \e\fR
          \fB\-\-slave=root@localhost:3307 \-\-rpl\-user=rpl:rpl \e\fR
          \fB\-\-master\-log\-file=my_log\&.000001 \-\-master\-log\-pos=96\fR
 # master on localhost: \&.\&.\&. connected\&.
 # slave on localhost: \&.\&.\&. connected\&.
 # Checking for binary logging on master\&.\&.\&.
 # Setting up replication\&.\&.\&.
 # \&.\&.\&.done\&.
.fi
.if n \{\
.RE
.\}
.sp
RECOMMENDATIONS.PP
You should set
read_only=1
in the
my\&.cnf
file for the slave to ensure that no accidental data changes, such as
\fBINSERT\fR,
\fBDELETE\fR,
\fBUPDATE\fR, and so forth, are permitted on the slave other than those produced by events read from the master\&.
.PP
Use the
\fB\-\-pedantic\fR
and
\fB\-vv\fR
options for setting up replication on production servers to avoid possible problems with differing storage engines\&.
PERMISSIONS REQUIRED.PP
The users on the master need the following privileges: SELECT and INSERT privileges on mysql database, REPLICATION SLAVE, REPLICATION CLIENT and GRANT OPTION\&. The slave users need the SUPER privilege\&. The repl user, used as the argument for the
\fB\-\-rpl\-user\fR
option, is either created automatically or if it exists, it needs the REPLICATION SLAVE privilege\&.
.SH "COPYRIGHT"
.br
.PP
Copyright \(co 2006, 2017, Oracle and/or its affiliates. All rights reserved.
.PP
This documentation is free software; you can redistribute it and/or modify it only under the terms of the GNU General Public License as published by the Free Software Foundation; version 2 of the License.
.PP
This documentation is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.
.PP
You should have received a copy of the GNU General Public License along with the program; if not, write to the Free Software Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA or see http://www.gnu.org/licenses/.
.sp
.SH "SEE ALSO"
For more information, please refer to the MySQL Utilities and Fabric
documentation, which is available online at
http://dev.mysql.com/doc/index-utils-fabric.html
.SH AUTHOR
Oracle Corporation (http://dev.mysql.com/).
